package com.yc.excel;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

/**
 * Title:<br>
 * Description: <br>
 * Author:陈圆(chenyuan@bosssoft.com.cn) <br>
 * Date:2020/9/14
 */
public class ExcelVersion2 {

    public static void main(String[] args) {
        ExcelVersion2 excelVersion2 = new ExcelVersion2();
        try {
            //获取指定行的值
//            readSpecifyRows(new File("D:\\wenan\\111.xls"));
//            excelVersion2.readByCellTitle(new File("D:\\test.xls"));
            excelVersion2.readByCellTitle(new File("/Users/samuelchen/Downloads/短信模版初始化数据表头.xlsx"));
            //读取行列的值
//            readRowsAndColums(new File("D:\\wenan\\111.xls"));

            //将获取到的值写入到TXT或者xls中
//            copy_excel(new File("D:\\wenan\\111.xls"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    private static final String[] arr = {
            "列名",
            "字段类型"
    };

    public static Map<String, String> getHashMap() {
        Map<String, String> map = new HashMap<>();
        map.put("String", "varchar2");
        map.put("NString", "varchar2");
        map.put("GBString", "varchar2");
        map.put("Date", "varchar2(16)");
        map.put("DateTime", "varchar2(16)");
        map.put("Currency", "Number(18, 6)");
        map.put("Integer", "Number(1)");

        return map;
    }

    public static void processString() {
        String bb = "(ADJ_SUB_ID,指标调整明细主键,String,38,1),(MOF_DIV_CODE,财政区划代码,NString,9,1),(FISCAL_YEAR,预算年度,NString,4,1),(COR_BGT_DOC_NO,本级指标文号,GBString,100,1),(BGT_DOC_TITLE,指标文标题,GBString,360,1),(DOC_DATE,发文时间,Date,0,1),(BGT_DEC,指标说明,GBString,360,1),(PRO_CODE,项目代码,String,21,1),(BAT_NUM,调整批次号,String,6,1),(SOURCE_TYPE_CODE,指标来源代码,NString,1,1),(ADJ_DATE,调整日期,Date,0,1),(BGT_ID,调出/调入指标ID,String,38,1),(AMOUNT,调出/调入金额,Currency,0,1),(ORI_BGT_ID,源指标主键,String,38,0),(FUND_TYPE_CODE,资金性质代码,NString,8,1),(EXP_FUNC_CODE,支出功能分类科目代码,NString,7,1),(GOV_BGT_ECO_CODE,政府支出经济分类代码,NString,7,0),(DEP_BGT_ECO_CODE,部门支出经济分类代码,NString,7,0),(UPDATE_TIME,更新时间,DateTime,0,1),(REC_DIV_CODE,接收方财政区划代码,NString,9,1),(BGT_MOF_DEP_CODE,指标管理处室代码,NString,6,1),(MANAGE_MOF_DEP_CODE,业务主管处室代码,NString,6,1),(IS_TRACK,是否追踪,Integer,1,1),(BUDGET_LEVEL_CODE,预算级次代码,NString,1,1),(IS_DELETED,是否删除,Integer,1,1),(CREATE_TIME,创建时间,DateTime,0,1)";
        String[] split = bb.split("\\),");
        int length = split.length;

        for (String s : split) {
            String[] split1 = s.substring(1).split(",");
            for (String s1 : split1) {

            }

            System.out.println();
        }
    }



    private void readByCellTitle(File file) throws Exception{
        InputStream in = new FileInputStream(file.getAbsoluteFile());
        Workbook workbook = Workbook.getWorkbook(in);
        String[] sheetCellTitle = new String[]{"列名", "字段类型"};
        int numberOfSheets = workbook.getNumberOfSheets(); // 获取sheet数量
        for (int i=0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheet(i);
            int rowsNum = sheet.getRows(); // 获取行数
            for (int j=0; j < rowsNum; j++) {
                for (String cellTitle : sheetCellTitle) {
                    Cell cellTitle1 = sheet.getCell(cellTitle);
                    System.out.println(cellTitle1.getContents());
                }
            }
        }

    }
    /**
     *  	读取指定列
     * @param file
     * @throws Exception
     */
    public static String readSpecifyColumns(File file)throws Exception{
        ArrayList<String> columnList = new ArrayList<String>();
        ArrayList<String> valueList1 = new ArrayList<String>();
        ArrayList<String> valueList2 = new ArrayList<String>();
        ArrayList<String> valueList3 = new ArrayList<String>();
        ArrayList<String> valueList4 = new ArrayList<String>();
        Workbook readwb = null;
        InputStream io = new FileInputStream(file.getAbsoluteFile());
        readwb = Workbook.getWorkbook(io);
        Sheet readsheet = readwb.getSheet(0);
        int rsColumns = readsheet.getColumns();  //获取表格列数
        int rsRows = readsheet.getRows();  //获取表格行数
        for (int i = 0; i < rsRows; i++) {
            Cell cell_name = readsheet.getCell(0, i);  //第一列的值
            columnList.add(cell_name.getContents());
            Cell cell_value1 = readsheet.getCell(1, i);  //第2列的值，此处需要手动更改，获取不同列的值
            valueList1.add(cell_value1.getContents());
            Cell cell_value2 = readsheet.getCell(2, i);  //第3列的值，此处需要手动更改，获取不同列的值
            valueList2.add(cell_value2.getContents());
            Cell cell_value3 = readsheet.getCell(3, i);  //第4列的值，此处需要手动更改，获取不同列的值
            valueList3.add(cell_value3.getContents());
//            Cell cell_value4 = readsheet.getCell(4, i);  //第5列的值，此处需要手动更改，获取不同列的值
//            valueList4.add(cell_value4.getContents());
        }
//        System.out.println(columnList);
//        System.out.println(valueList);

//        String[] name_String = new String[columnList.size()];
//        String[] value_String = new String[columnList.size()];
        StringBuffer buffer = new StringBuffer(16);
        String result = "";
        for (int i = 0; i < columnList.size(); i++) {
            // 以下情况会有括号
            boolean hasPat = "varchar2".equals(getHashMap().get(valueList1.get(i))) ;
            String pa =  hasPat ? "(" + valueList2.get(i) + ")" : "";
            String isNullStr = "M".equals(valueList3.get(i)) ? "not null" : "";

            result = columnList.get(i) + " " + getHashMap().get(valueList1.get(i)) + pa + " " + isNullStr + ", \n";
            buffer.append(result);
//            name_String[i] = columnList.get(i);
//            value_String[i] = valueList1.get(i);
//            value_String[i] = valueList1.get(i);
//			System.out.println("<string name=" + "\"" + name_String[i] + "\">" + value_String[i] +  "</string>");
        }
        return buffer.toString().substring(0, buffer.toString().length()-3) ;
    }

    /**
     *   	读取指定行
     * @param file
     * @throws Exception
     */
    public static void readSpecifyRows(File file)throws Exception{
        ArrayList<String> columnList = new ArrayList<String>();
        Workbook readwb = null;
        InputStream io = new FileInputStream(file.getAbsoluteFile());
        readwb = Workbook.getWorkbook(io);
        Sheet readsheet = readwb.getSheet(0);
        int rsColumns = readsheet.getColumns();  //获取表格列数
        int rsRows = readsheet.getRows();  //获取表格行数
        for (int i = 1; i < rsColumns; i++) {
            Cell cell_name = readsheet.getCell(i, 1);  //在这里指定行，此处需要手动更改，获取不同行的值
            columnList.add(cell_name.getContents());
        }
        System.out.println(columnList);
    }


    private static void readRowsAndColums(File file) throws BiffException, IOException {
        //1:创建workbook
        Workbook workbook=Workbook.getWorkbook(new File("D:\\wenan\\111.xls"));
        //2:获取第一个工作表sheet
        Sheet sheet=workbook.getSheet(0);
        //3:获取数据
        System.out.println("行："+sheet.getRows());
        System.out.println("列："+sheet.getColumns());
        for(int i=0;i<sheet.getRows();i++){
            for(int j=0;j<sheet.getColumns();j++){
                Cell cell=sheet.getCell(j,i);
                System.out.print(cell.getContents()+" ");
            }
            System.out.println();
        }

        //最后一步：关闭资源
        workbook.close();
    }

    /**
     * 	将获取到的值写入到TXT或者xls中
     * @param file
     * @throws Exception
     */
    public static void copy_excel(File file) throws Exception {
        FileWriter fWriter = null;
        PrintWriter out = null;
        String fliename = file.getName().replace(".xls", "");
        fWriter = new FileWriter(file.getParent()+ "/agetwo.xls");//输出格式为.xls
        fWriter = new FileWriter(file.getParent() + "/" + fliename + ".txt");//输出格式为.txt
        out = new PrintWriter(fWriter);
        InputStream is = new FileInputStream(file.getAbsoluteFile());
        Workbook wb = null;
        wb = Workbook.getWorkbook(is);
        int sheet_size = wb.getNumberOfSheets();
        Sheet sheet = wb.getSheet(0);
        for (int j = 1; j < sheet.getRows(); j++) {
            String cellinfo = sheet.getCell(0, j).getContents();//读取的是第二列数据，没有标题，标题起始位置在for循环中定义
            out.println(cellinfo);
        }
        out.close();//关闭流
        fWriter.close();
        out.flush();//刷新缓存
        System.out.println("输出完成！");
    }
}
